import pandas as pd
import mysql.connector

# 第一步：加载Excel文件
excel_path = '/Users/xiong/Downloads/款到发货客户.xlsx'
df = pd.read_excel(excel_path, sheet_name='Sheet1')

# 第二步：从Excel中提取客户编码列
customer_code_column = df['客户编码']

# 第三步：连接MySQL数据库
connection = mysql.connector.connect(
    host='rm-uf6h8t41a4h5120a1uo.mysql.rds.aliyuncs.com',  # 数据库主机地址
    user='sanhua_admin',  # 数据库用户名
    password='Sanhua123!',  # 数据库密码
    database='sanhua'  # 数据库名称
)

cursor = connection.cursor()

# 第四步：遍历每一行，根据客户编码更新customers表的isPaymentUponDelivery字段
for customer_code in customer_code_column:
    # 更新customers表
    update_query = """
    UPDATE customers
    SET isPaymentUponDelivery = 1
    WHERE CAST(code AS CHAR) = %s AND type = 2
    """
    cursor.execute(update_query, (customer_code,))

# 第五步：提交事务并关闭连接
connection.commit()
cursor.close()
connection.close()
